package com.j.lemon.learn.basic.excel;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @Author lijunjun
 * @Date 2018-12-17 14:27
 * @Description
 */
public class ExcelColor {
    public static void main(String[] args) {
        try {
            String sExcelPath = "/Users/lijunjun/Desktop/22.xlsx";
            File file = new File(sExcelPath);
            FileInputStream inputStream = new FileInputStream(file);
            Workbook workbook = WorkbookFactory.create(inputStream);
            resetStyle(workbook);
//            Sheet sheet = workbook.getSheetAt(1);

//            //设置背景色
//            CellStyle cellStyle = workbook.createCellStyle();
//            cellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
//            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//
//
//            //修改单元格颜色
//            Row row = sheet.getRow(1);
////            Cell cell = row.getCell(1);
////            cell.setCellStyle(cellStyle);
//            row.setRowStyle(cellStyle);


            //对修改后的Excel进行保存
            FileOutputStream excelFileOutPutStream = new FileOutputStream(file.getAbsolutePath());
            workbook.write(excelFileOutPutStream);
            excelFileOutPutStream.flush();
            excelFileOutPutStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static void resetStyle(Workbook workbook){
        Sheet sheet = workbook.getSheetAt(1);
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        Row row0 = sheet.getRow(0);
        int physicalNumberOfCells = row0.getPhysicalNumberOfCells();

        for(int i =physicalNumberOfRows-1;i>0;i--){
            Row row = sheet.getRow(i);
            int num = 0;
            for(int j = 0;j<physicalNumberOfCells;j++){
                Cell cell = row.getCell(j);
                if(StringUtils.isBlank(cell.getStringCellValue())){
                    num++;
                }
            }
            if(num==physicalNumberOfCells){
                sheet.removeRow(row);
            }
        }

        int rowsAfter = sheet.getPhysicalNumberOfRows();
        int cellsAfter = row0.getPhysicalNumberOfCells();

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.WHITE.index);

        cellStyle.setBorderBottom(BorderStyle.THIN);

        cellStyle.setBorderTop(BorderStyle.THIN);

        cellStyle.setBorderLeft(BorderStyle.THIN);

        cellStyle.setBorderRight(BorderStyle.THIN);


        for(int i = 1; i<rowsAfter;i++){
            Row row = sheet.getRow(i);
            for(int j =0;j<cellsAfter ;j++){
                Cell cell = row.getCell(j);
                if(cell==null){
                    cell=row.createCell(j);
                }
                cell.setCellStyle(cellStyle);

            }
        }
    }
}
